<?php


class EASYNC_fk_information {

    /**
     *
     * @var modelo_dbm
     */
    protected $conn = NULL;
    protected $database = "";

    public function __construct($database) {
        $this->conn = model_conn_information_schema::get_conn();
        $this->database = $database;
    }

    public function getInformation() {
        $tables_with_fk = $this->getTablesWithFk();
        $tables_information = array();

        if(!empty($tables_with_fk)) {
            foreach ($tables_with_fk as $table) {
                $ti = $this->getTableInformation($table);
                $tables_information[$table] = $ti;
            }
        }
        model_conn_information_schema::get_conn()->closeConnection();

        return $tables_information;
    }

    protected function getTablesWithFk() {
        $q = "
            
SELECT k.TABLE_NAME
FROM 
KEY_COLUMN_USAGE k, 
TABLE_CONSTRAINTS tc
WHERE 
tc.CONSTRAINT_SCHEMA = '$this->database'
AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
GROUP BY tc.TABLE_NAME

";
        $r = $this->conn->qcv($q, 'TABLE_NAME');
        return $r;
    }

    protected function getTableInformation($table_name) {

        $q = "
            
SELECT

k.TABLE_NAME, 
k.COLUMN_NAME, 
k.REFERENCED_TABLE_NAME, 
k.REFERENCED_COLUMN_NAME

FROM 

KEY_COLUMN_USAGE k,
TABLE_CONSTRAINTS tc

WHERE 

tc.CONSTRAINT_SCHEMA = '$this->database'
AND tc.TABLE_NAME = '$table_name'
AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME

";

        $r = $this->conn->qcv($q, 'COLUMN_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME');

        $resp = array();

        foreach ($r as $v) {

            $prov = array(
                'COLUMN_NAME' => $v[0],
                'REFERENCED_TABLE_NAME' => $v[1],
                'REFERENCED_COLUMN_NAME' => $v[2]
            );
            array_push($resp, $prov);
        }
        return $resp;
    }

}

?>
